package org.crc.myvhrb.utils;

import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.crc.myvhrb.model.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * CRC
 * 2021/8/16
 */
public class POIUtils {

    /**
     * 导出数据，将数据库中的员工数据转换成 Excel 表格导出
     *
     * @param list
     * @return
     */
    public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {
        // 1. 创建一个 Excel 文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 2. 创建文档摘要
        workbook.createInformationProperties();
        // 3. 获取并配置文档信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        // 文档类别
        docInfo.setCategory("员工信息");
        // 文档管理员
        docInfo.setManager("anyCcCN");
        // 设置公司信息
        docInfo.setCompany("www.anyCcCN.org");
        // 4. 获取文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        // 文档标题
        summInfo.setTitle("员工信息表");
        // 文档作者
        summInfo.setAuthor("anyCcCN");
        // 文档备注
        summInfo.setComments("本文档由 anyCcCN 提供");
        // 5. 创建样式，主要设置 Excel 表格的背景色，宽度等样式
        // 创建标题行的样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 设置背景色为 YELLOW
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        // 模式
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 创建日期样式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        // 6. 创建表
        HSSFSheet sheet = workbook.createSheet("员工信息表");
        // 创建标题行
        HSSFRow r0 = sheet.createRow(0);
        // 第一行第一列数据，以此类推
        HSSFCell c0 = r0.createCell(0);
        c0.setCellValue("编号");
        c0.setCellStyle(headerStyle);
        HSSFCell c1 = r0.createCell(1);
        c1.setCellValue("姓名");
        c1.setCellStyle(headerStyle);
        HSSFCell c2 = r0.createCell(2);
        c2.setCellValue("工号");
        c2.setCellStyle(headerStyle);
        HSSFCell c3 = r0.createCell(3);
        c3.setCellValue("性别");
        c3.setCellStyle(headerStyle);
        HSSFCell c4 = r0.createCell(4);
        c4.setCellValue("出生日期");
        c4.setCellStyle(headerStyle);
        HSSFCell c5 = r0.createCell(5);
        c5.setCellValue("身份证号码");
        c5.setCellStyle(headerStyle);
        HSSFCell c6 = r0.createCell(6);
        c6.setCellValue("婚姻状况");
        c6.setCellStyle(headerStyle);
        HSSFCell c7 = r0.createCell(7);
        c7.setCellValue("民族");
        c7.setCellStyle(headerStyle);
        HSSFCell c8 = r0.createCell(8);
        c8.setCellValue("籍贯");
        c8.setCellStyle(headerStyle);
        HSSFCell c9 = r0.createCell(9);
        c9.setCellValue("政治面貌");
        c9.setCellStyle(headerStyle);
        HSSFCell c10 = r0.createCell(10);
        c10.setCellValue("电话号码");
        c10.setCellStyle(headerStyle);
        HSSFCell c11 = r0.createCell(11);
        c11.setCellValue("联系地址");
        c11.setCellStyle(headerStyle);
        HSSFCell c12 = r0.createCell(12);
        c12.setCellValue("所属部门");
        c12.setCellStyle(headerStyle);
        HSSFCell c13 = r0.createCell(13);
        c13.setCellValue("职称");
        c13.setCellStyle(headerStyle);
        HSSFCell c14 = r0.createCell(14);
        c14.setCellValue("职位");
        c14.setCellStyle(headerStyle);
        HSSFCell c15 = r0.createCell(15);
        c15.setCellValue("聘用形式");
        c15.setCellStyle(headerStyle);
        HSSFCell c16 = r0.createCell(16);
        c16.setCellValue("最高学历");
        c16.setCellStyle(headerStyle);
        HSSFCell c17 = r0.createCell(17);
        c17.setCellValue("专业");
        c17.setCellStyle(headerStyle);
        HSSFCell c18 = r0.createCell(18);
        c18.setCellValue("毕业院校");
        c18.setCellStyle(headerStyle);
        HSSFCell c19 = r0.createCell(19);
        c19.setCellValue("入职日期");
        c19.setCellStyle(headerStyle);
        HSSFCell c20 = r0.createCell(20);
        c20.setCellValue("在职状态");
        c20.setCellStyle(headerStyle);
        HSSFCell c21 = r0.createCell(21);
        c21.setCellValue("邮箱");
        c21.setCellStyle(headerStyle);
        HSSFCell c22 = r0.createCell(22);
        c22.setCellValue("合同期限(年)");
        c22.setCellStyle(headerStyle);
        HSSFCell c23 = r0.createCell(23);
        c23.setCellValue("合同起始日期");
        c23.setCellStyle(headerStyle);
        HSSFCell c24 = r0.createCell(24);
        c24.setCellValue("合同终止日期");
        c24.setCellStyle(headerStyle);
        HSSFCell c25 = r0.createCell(25);
        c25.setCellValue("转正");
        c25.setCellStyle(headerStyle);

        // 7. 将员工数据加载到 Excel 表格中
        for (int i = 0; i < list.size(); i++) {
            Employee emp = list.get(i);
            // 第 0 行为标题行
            HSSFRow row = sheet.createRow(i + 1);
            // 为每一列添加数据
            row.createCell(0).setCellValue(emp.getId());
            row.createCell(1).setCellValue(emp.getName());
            row.createCell(2).setCellValue(emp.getWorkID());
            row.createCell(3).setCellValue(emp.getGender());
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(dateCellStyle);
            cell4.setCellValue(emp.getBirthday());
            row.createCell(5).setCellValue(emp.getIdCard());
            row.createCell(6).setCellValue(emp.getWedlock());
            row.createCell(7).setCellValue(emp.getNation().getName());
            row.createCell(8).setCellValue(emp.getNativePlace());
            row.createCell(9).setCellValue(emp.getPoliticsstatus().getName());
            row.createCell(10).setCellValue(emp.getPhone());
            row.createCell(11).setCellValue(emp.getAddress());
            row.createCell(12).setCellValue(emp.getDepartment().getName());
            row.createCell(13).setCellValue(emp.getJobLevel().getName());
            row.createCell(14).setCellValue(emp.getPosition().getName());
            row.createCell(15).setCellValue(emp.getEngageForm());
            row.createCell(16).setCellValue(emp.getTiptopDegree());
            row.createCell(17).setCellValue(emp.getSpecialty());
            row.createCell(18).setCellValue(emp.getSchool());
            HSSFCell cell19 = row.createCell(19);
            cell19.setCellStyle(dateCellStyle);
            cell19.setCellValue(emp.getBeginDate());
            row.createCell(20).setCellValue(emp.getWorkState());
            row.createCell(21).setCellValue(emp.getEmail());
            row.createCell(22).setCellValue(emp.getContractTerm());
            HSSFCell cell23 = row.createCell(23);
            cell23.setCellStyle(dateCellStyle);
            cell23.setCellValue(emp.getBeginContract());
            HSSFCell cell24 = row.createCell(24);
            cell24.setCellStyle(dateCellStyle);
            cell24.setCellValue(emp.getEndContract());
            HSSFCell cell25 = row.createCell(25);
            cell25.setCellStyle(dateCellStyle);
            cell25.setCellValue(emp.getConversionTime());
        }

//        ByteArrayOutputStream baos = new ByteArrayOutputStream();
//        HttpHeaders headers = new HttpHeaders();
//
//        try {
//            // 将创建好的 Excel 表格转成 byte[] 形式
//            workbook.write(baos);
//            // 设置文件头
//            headers.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));
//            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//        // 参数1 Excel数据，参数2 文件头数据，参数3 模式
//        return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try {
            headers.setContentDispositionFormData("attachment", new String("员工表.xls".getBytes("UTF-8"), "ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    /**
     * 导入数据，将 Excel 表格数据转成 List 数组，
     * 由于在数据库中的 员工表并不直接保存民族、政治面貌、部门、职位、职称等数据的名称，而是以 id 的形式保存，
     * 因此需要以下几个参数进行转换
     *
     * @param file           上传的 Excel 表格数据
     * @param allNations     所有民族数据
     * @param allPoliticss   所有政治面貌数据
     * @param allDepartments 所有部门数据
     * @param allPosition    所有职位数据
     * @param allJobLevel    所有职称数据
     * @return
     */
    public static List<Employee> excel2Employee(MultipartFile file, List<Nation> allNations,
                                                List<Politicsstatus> allPoliticss, List<Department> allDepartments,
                                                List<Position> allPosition, List<JobLevel> allJobLevel) {
        // 保存员工数据，最终 return
        List<Employee> list = new ArrayList<>();
        Employee employee = null;
        try {
            // 1. 通过 file 创建一个 workbook 对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            // 2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                // 3. 获取表单
                HSSFSheet sheet = workbook.getSheetAt(i);
                // 4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    // 5. 第一行为标题行，跳过标题行
                    if (j == 0) {
                        continue;
                    }
                    // 6. 获取行
                    HSSFRow row = sheet.getRow(j);
                    // 如果为null，表示该行是空行，防止数据中间有空行
                    if (row == null) {
                        continue;
                    }
                    // 7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    employee = new Employee();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        // 获取列
                        HSSFCell cell = row.getCell(k);
                        // 由于表格中保存着不同的数据类型。因此通过 switch 的方式遍历
                        // 通过类型
                        switch (cell.getCellType()) {
                            // 如果类型为 string
                            case STRING:
                                // 获取数据
                                String cellValue = cell.getStringCellValue();
                                // 通过表格列位置判断那些需要转成 string
                                switch (k) {
                                    case 1:
                                        employee.setName(cellValue);
                                        break;
                                    case 2:
                                        employee.setWorkID(cellValue);
                                        break;
                                    case 3:
                                        employee.setGender(cellValue);
                                        break;
                                    case 5:
                                        employee.setIdCard(cellValue);
                                        break;
                                    case 6:
                                        employee.setWedlock(cellValue);
                                        break;
                                    case 7:
                                        // 在员工表中保存的是 id 而不是名称，因此需要进行以下转换
                                        // 通过民族名获取民族的下标，然后根据下标获取民族 id ,这是一种简约的方式。也可用通过民族名称查询数据库中对应的 id
                                        int index = allNations.indexOf(new Nation(cellValue));
                                        employee.setNationId(allNations.get(index).getId());
                                        break;
                                    case 8:
                                        employee.setNativePlace(cellValue);
                                        break;
                                    case 9:
                                        int indexOf = allPoliticss.indexOf(new Politicsstatus(cellValue));
                                        Integer id1 = allPoliticss.get(indexOf).getId();
                                        employee.setPoliticId(id1);
                                        break;
                                    case 10:
                                        employee.setPhone(cellValue);
                                        break;
                                    case 11:
                                        employee.setAddress(cellValue);
                                        break;
                                    case 12:
                                        int i1 = allDepartments.indexOf(new Department(cellValue));
                                        employee.setDepartmentId(allDepartments.get(i1).getId());
                                        break;
                                    case 13:
                                        int indexOf2 = allJobLevel.indexOf(new JobLevel(cellValue));
                                        employee.setJobLevelId(allJobLevel.get(indexOf2).getId());
                                        break;
                                    case 14:
                                        int indexOf3 = allPosition.indexOf(new Position(cellValue));
                                        employee.setPosId(allPosition.get(indexOf3).getId());
                                        break;
                                    case 15:
                                        employee.setEngageForm(cellValue);
                                        break;
                                    case 16:
                                        employee.setTiptopDegree(cellValue);
                                        break;
                                    case 17:
                                        employee.setSpecialty(cellValue);
                                        break;
                                    case 18:
                                        employee.setSchool(cellValue);
                                        break;
                                    case 20:
                                        employee.setWorkState(cellValue);
                                        break;
                                    case 21:
                                        employee.setEmail(cellValue);
                                        break;
                                }
                                break;
                            default: {
                                // 剩下的以时间格式转储
                                switch (k) {
                                    case 4:
                                        employee.setBirthday(cell.getDateCellValue());
                                        break;
                                    case 19:
                                        employee.setBeginDate(cell.getDateCellValue());
                                        break;
                                    case 23:
                                        employee.setBeginContract(cell.getDateCellValue());
                                        break;
                                    case 24:
                                        employee.setEndContract(cell.getDateCellValue());
                                        break;
                                    case 22:
                                        employee.setContractTerm(cell.getNumericCellValue());
                                        break;
                                    case 25:
                                        employee.setConversionTime(cell.getDateCellValue());
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    list.add(employee);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
}
